#-*- coding: utf-8 -*-
#autor: Sérgio Sardo 12677
#data: 08 de outubro de 2014
#obs.: trabalho prático de avaliação 1 - python

import xlrd, xlwt, sqlite3, sqlalchemy
from xlrd import open_workbook, cellname

book = open_workbook('IPC_Portugal_1977_2013.xls')
sheet = book.sheet_by_index(0)
print 'name: ', sheet.name
print 'rows: ', sheet.nrows
print 'columns: ', sheet.ncols

'''
for row_index in range(sheet.nrows):
    for col_index in range(sheet.ncols):
        print cellname(row_index, col_index), '-', sheet.cell(row_index, col_index).value
'''
lista = []
linhas = []

conn = sqlite3.connect('tp1.db')
c = conn.cursor()
c.execute('DROP TABLE ipc')
c.execute ('CREATE TABLE IF NOT EXISTS ipc (ano integer, ipc_global real, va real, rem_min real, rem_max real, pib real, rend_nac real, rend_disp real, rend_an real)')

for nrow in range(sheet.nrows):
    for ncol in range(1, sheet.ncols):
        linhas.append(sheet.cell(nrow, ncol).value)
    lista.append(linhas)
    c.execute('INSERT INTO ipc VALUES (?,?,?,?,?,?,?,?,?)', linhas)    
    linhas = [] #empties list linhas

conn.commit()
conn.close()
